unit teknikdurumy;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  ppVar, ppBands, ppCtrls, ppPrnabl, ppClass, ppCache, ppProd, ppReport,
  ppDB, ppComm, ppRelatv, ppDBPipe, Db, ADODB, Grids, Wwdbigrd, Wwdbgrid,
  StdCtrls, wwdbdatetimepicker, Wwdotdot, Wwdbcomb, wwdblook, Mask,
  wwdbedit, ExtCtrls, ppViewr, variants, TXComp, TXRB;

type
  Ttekdurum = class(TForm)
    wwDBGrid1: TwwDBGrid;
    qteklst: TADOQuery;
    DataSource1: TDataSource;
    ppDBPipeline1: TppDBPipeline;
    report: TppReport;
    Bevel1: TBevel;
    Label15: TLabel;
    Label14: TLabel;
    BitTar: TwwDBDateTimePicker;
    basTar: TwwDBDateTimePicker;
    CheckBox2: TCheckBox;
    CheckBox7: TCheckBox;
    CheckBox8: TCheckBox;
    CheckBox13: TCheckBox;
    CheckBox11: TCheckBox;
    CheckBox1: TCheckBox;
    CheckBox10: TCheckBox;
    Tekniktipi: TADOQuery;
    TekniktipiS: TDataSource;
    ArizaTuruS: TDataSource;
    ArizaTuru: TADOQuery;
    Yapilanislem: TADOQuery;
    YapilanislemS: TDataSource;
    grups: TDataSource;
    grup: TADOQuery;
    model: TADOQuery;
    models: TDataSource;
    markaS: TDataSource;
    marka: TADOQuery;
    agrup: TADOQuery;
    agrups: TDataSource;
    tipi: TwwDBLookupCombo;
    ArzTuru: TwwDBLookupCombo;
    yapisl: TwwDBLookupCombo;
    ppHeaderBand1: TppHeaderBand;
    ppDetailBand1: TppDetailBand;
    ppFooterBand1: TppFooterBand;
    ppTitleBand1: TppTitleBand;
    ppLabel1: TppLabel;
    ppSystemVariable1: TppSystemVariable;
    ppSystemVariable2: TppSystemVariable;
    ppDBText1: TppDBText;
    ppLabel2: TppLabel;
    ppDBText2: TppDBText;
    ppLabel3: TppLabel;
    ppDBText3: TppDBText;
    ppLabel4: TppLabel;
    ppDBText4: TppDBText;
    ppLabel5: TppLabel;
    ppDBText5: TppDBText;
    ppLabel6: TppLabel;
    ppDBText6: TppDBText;
    ppLabel7: TppLabel;
    ppDBText7: TppDBText;
    ppLabel8: TppLabel;
    ppDBText8: TppDBText;
    ppLabel9: TppLabel;
    ppDBText9: TppDBText;
    ppLabel10: TppLabel;
    ppDBText10: TppDBText;
    ppLabel11: TppLabel;
    ppDBText11: TppDBText;
    ppLabel12: TppLabel;
    ppDBText12: TppDBText;
    ppLabel13: TppLabel;
    ppDBText13: TppDBText;
    ppLabel14: TppLabel;
    ppDBText14: TppDBText;
    ppLabel15: TppLabel;
    ppDBText15: TppDBText;
    ppLabel16: TppLabel;
    ppLine1: TppLine;
    ppLine2: TppLine;
    ppSummaryBand1: TppSummaryBand;
    ppLabel17: TppLabel;
    ppDBCalc1: TppDBCalc;
    ppLine3: TppLine;
    ppDBCalc2: TppDBCalc;
    ppDBCalc3: TppDBCalc;
    ppDBCalc4: TppDBCalc;
    ppDBCalc5: TppDBCalc;
    ppDBCalc6: TppDBCalc;
    ppDBCalc7: TppDBCalc;
    ppDBCalc8: TppDBCalc;
    ppDBCalc9: TppDBCalc;
    ppDBCalc10: TppDBCalc;
    ppDBCalc11: TppDBCalc;
    ppDBCalc12: TppDBCalc;
    ppDBCalc13: TppDBCalc;
    ppDBCalc14: TppDBCalc;
    ppLabel18: TppLabel;
    ppLabel19: TppLabel;
    ppLabel20: TppLabel;
    ppLabel21: TppLabel;
    ppLabel22: TppLabel;
    d1: TppLabel;
    d2: TppLabel;
    d3: TppLabel;
    d4: TppLabel;
    d5: TppLabel;
    ppLabel23: TppLabel;
    ppLabel24: TppLabel;
    ppLabel25: TppLabel;
    ppLabel26: TppLabel;
    d6: TppLabel;
    d7: TppLabel;
    d8: TppLabel;
    d9: TppLabel;
    Panel2: TPanel;
    pKapat: TPanel;
    Button2: TButton;
    Button3: TButton;
    Button1: TButton;
    Label1: TLabel;
    ppLabel27: TppLabel;
    ppDBText16: TppDBText;
    ppDBCalc15: TppDBCalc;
    filtre1k: TwwDBLookupCombo;
    filtre2k: TwwDBLookupCombo;
    filtre3k: TwwDBLookupCombo;
    filtre4k: TwwDBLookupCombo;
    ExtraOptions1: TExtraOptions;
    procedure FormClose(Sender: TObject; var Action: TCloseAction);
    procedure Button3Click(Sender: TObject);
    procedure Button1Click(Sender: TObject);
    procedure FirmaAdiKeyPress(Sender: TObject; var Key: Char);
    procedure CheckBox2Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure wwDBGrid1CalcTitleAttributes(Sender: TObject;
      AFieldName: string; AFont: TFont; ABrush: TBrush;
      var ATitleAlignment: TAlignment);
    procedure Button2Click(Sender: TObject);
    procedure CheckBox7Click(Sender: TObject);
    procedure CheckBox8Click(Sender: TObject);
    procedure CheckBox10Click(Sender: TObject);
    procedure CheckBox1Click(Sender: TObject);
    procedure CheckBox13Click(Sender: TObject);
    procedure CheckBox11Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure reportPreviewFormCreate(Sender: TObject);
    procedure wwDBGrid1CalcTitleImage(Sender: TObject; Field: TField;
      var TitleImageAttributes: TwwTitleImageAttributes);
    procedure wwDBGrid1TitleButtonClick(Sender: TObject;
      AFieldName: string);
    procedure filtre1kChange(Sender: TObject);
    procedure filtre2kChange(Sender: TObject);
    procedure filtre3kChange(Sender: TObject);
    procedure filtre2kEnter(Sender: TObject);
    procedure filtre3kEnter(Sender: TObject);
    procedure filtre4kEnter(Sender: TObject);
  private
    { Private declarations }
  public
    aranan: string[50];
    siras: string[20];
    bk: string[10];

    { Public declarations }
  end;

var
  tekdurum: Ttekdurum;
  stokkod, carikod: integer;
  sira: string[25];
  bk: string[10];

implementation

uses data, udmTeknik, cariaral, ucariara2, stokaral, Unit2;

{$R *.DFM}

procedure Ttekdurum.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  tekniktipi.close;
  arizaturu.close;
  yapilanislem.close;
  grup.close;
  model.close;
  marka.close;
  agrup.close;

  action := cafree;
  tekdurum := nil;
end;

procedure Ttekdurum.Button3Click(Sender: TObject);
begin
  qTekLst.DisableControls;
  d1.Caption := bastar.Text;
  d2.Caption := bittar.Text;
  d3.Caption := tipi.text;
  d4.Caption := ArzTuru.text;
  d5.Caption := yapisl.text;

  d6.Caption := filtre1k.text;
  d7.Caption := filtre2k.text;
  d8.Caption := filtre3k.text;
  d9.Caption := filtre4k.text;

  report.Print;
  qTekLst.EnableControls;
end;

procedure Ttekdurum.Button1Click(Sender: TObject);
begin
  close;
end;

procedure Ttekdurum.FirmaAdiKeyPress(Sender: TObject; var Key: Char);
begin
  if key = #13 then
  begin
    Key := #0;
    Perform(CM_DialogKey, VK_TAB, 0);
  end;

end;

procedure Ttekdurum.CheckBox2Click(Sender: TObject);
begin
  if CheckBox2.Checked then
  begin
    Tipi.Enabled := true;
    Tipi.setfocus;
  end
  else
  begin
    Tipi.value := '';
    Tipi.Enabled := False;
  end;

end;

procedure Ttekdurum.FormCreate(Sender: TObject);
begin
  if not dmTeknik.sptekniktipi.Active then
    dmTeknik.sptekniktipi.Open;
end;

procedure Ttekdurum.wwDBGrid1CalcTitleAttributes(Sender: TObject;
  AFieldName: string; AFont: TFont; ABrush: TBrush;
  var ATitleAlignment: TAlignment);
begin
  if siras = AFieldName then
    AFont.Style := [fsBold]
  else
    AFont.Style := [];
end;

procedure Ttekdurum.Button2Click(Sender: TObject);
begin
  qTekLst.Close;
  qTekLst.SQL.Clear;
  qTekLst.SQL.Add('select serinos,fatharhar.stokkod,stok.stokkodu,stok.grup,stok.model,stok.marka,stok.agrup,');
  qTekLst.SQL.Add('tipi=(select top 1 tipi=(select tipi from teknik where teknik.formno=onarim.formno) from onarim where serino=fatharhar.serinos order by onarim.formno),');
  qTekLst.SQL.Add('arizaturu=(select top 1 (select Turu from ArizaTurleri Where id=onarim.arizaturu) from onarim where serino=fatharhar.serinos order by onarim.formno),');
  qTekLst.SQL.Add('yapislemler=(select top 1 yapislemler from onarim where serino=fatharhar.serinos order by onarim.formno),');
  qTekLst.SQL.Add('ciktarih=(select tarih from fatura where bilisno=fatharhar.bilisnocik),');
  qTekLst.SQL.Add('tekniktarih=(select top 1 tekniktarih=(select tarih from teknik where teknik.formno=onarim.formno) from onarim where serino=fatharhar.serinos order by onarim.formno) ');
  qTekLst.SQL.Add('into #yeni1 from fatharhar,stok ');
  qTekLst.SQL.Add('where (bilisnocik is not null) and (fatharhar.stokkod=stok.stokkod) ');
  qTekLst.SQL.Add('and ((select tarih from fatura where bilisno=fatharhar.bilisnocik)>:bastar) ');
  qTekLst.SQL.Add('and ((select tarih from fatura where bilisno=fatharhar.bilisnocik)<:bittar)');
  qTekLst.Parameters.ParamByName('BasTar').datatype := ftDate;
  qTekLst.Parameters.ParamByName('BitTar').datatype := ftDate;
  qTekLst.Parameters.ParamByName('BasTar').value := BasTar.date;
  qTekLst.Parameters.ParamByName('BitTar').value := BitTar.date + 1;

  if (filtre1k.Text <> '') or (filtre2k.Text <> '') or (filtre3k.Text <> '') or
    (filtre4k.Text <> '') then
  begin
    qTekLst.SQL.Add('and stok.stokkod in (select stok.stokkod from stok where 1=1 ');
    if (filtre1k.Text <> '') then
      qTekLst.SQL.Add('and grup in (select grkod from stgrup where isim=' + #39
        + filtre1k.LookupValue + #39 + ')');
    if (filtre2k.Text <> '') then
      qTekLst.SQL.Add('and model in (select modelid from stmodel where modeladi='
        + #39 + filtre2k.LookupValue + #39 + ')');
    if (filtre3k.Text <> '') then
      qTekLst.SQL.Add('and marka in (select markaid from stmarkag where markadi='
        + #39 + filtre3k.LookupValue + #39 + ')');
    if (filtre4k.Text <> '') then
      qTekLst.SQL.Add('and agrup in (select grkod from stagrup where isim=' + #39
        + filtre4k.LookupValue + #39 + ')');
    qTekLst.SQL.Add(')');
  end;

  qTekLst.SQL.Add('select stokkod,abs(DATEDIFF(day,ciktarih,tekniktarih)/30)+1 as ay into #yeni2 from #yeni1 ');
  qTekLst.SQL.Add('where (stokkod>0) ');

  if CheckBox2.Checked then
    qTekLst.SQL.Add('and (tipi=''' + tipi.Lookupvalue + ''')');
  if CheckBox8.Checked then
    qTekLst.SQL.Add('and (arizaturu=''' + arzturu.Lookupvalue + ''')');
  if CheckBox7.Checked then
    qTekLst.SQL.Add('and (yapislemler=''' + yapisl.Lookupvalue + ''')');

  qTekLst.SQL.Add('select stokkod,stokkodu=(select top 1 stokkodu from stok where stok.stokkod=#yeni2.stokkod),');
  qTekLst.SQL.Add('a0=count(stokkod),a1=sum(CASE ay WHEN 1 then  1 else 0 END),a2=sum(CASE ay WHEN 2 then  1 else 0 END),');
  qTekLst.SQL.Add('a3=sum(CASE ay WHEN 3 then  1 else 0 END),a4=sum(CASE ay WHEN 4 then  1 else 0 END),a5=sum(CASE ay WHEN 5 then  1 else 0 END),');
  qTekLst.SQL.Add('a6=sum(CASE ay WHEN 6 then  1 else 0 END),a7=sum(CASE ay WHEN 7 then  1 else 0 END),a8=sum(CASE ay WHEN 8 then  1 else 0 END),');
  qTekLst.SQL.Add('a9=sum(CASE ay WHEN 9 then  1 else 0 END),a10=sum(CASE ay WHEN 10 then  1 else 0 END),a11=sum(CASE ay WHEN 11 then  1 else 0 END),');
  qTekLst.SQL.Add('a12=sum(CASE ay WHEN 12 then  1 else 0 END),a13=sum(CASE WHEN ay>12 and ay<24  then  1 else 0 END),a14=sum(CASE WHEN ay is null  then  1 else 0 END) ');
  qTekLst.SQL.Add('from #yeni2 group by stokkod ');

  {  qTekLst.SQL.Add('select stokkodu,a11=100*a1/a0,a22=100*a2/a0,a33=100*a3/a0,a44=100*a4/a0,a55=100*a5/a0,a66=100*a6/a0,');
    qTekLst.SQL.Add('a77=100*a7/a0,a88=100*a8/a0,a99=100*a9/a0,a1010=100*a10/a0,a1111=100*a11/a0,a1212=100*a12/a0,');
    qTekLst.SQL.Add('a1313=100*a13/a0,a1414=100*a14/a0 from #yeni3');
  //  qTekLst.SQL.Add('order by a11,a22,a33,a44,a55,a66,a77,a88,a99,a1010,a1111,a1212,a1313,a1414 desc');
    qTekLst.SQL.Add('order by a11 desc,a22 desc,a33 desc,a44 desc,a55 desc,a66 desc,a77 desc,a88 desc,a99 desc,a1010 desc,a1111 desc,a1212 desc,a1313 desc,a1414 desc');
  //  memo1.lines:=qTekLst.sql;

  //  memo1.lines:=qTekLst.sql;
  }
  qTekLst.Open;

  tbcdfield(qTekLst.FieldByName('a1')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a2')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a3')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a4')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a5')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a6')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a7')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a8')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a9')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a11')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a11')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a12')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a13')).displayformat := '#,0';
  tbcdfield(qTekLst.FieldByName('a14')).displayformat := '#,0';

  Label1.Caption := '(' + inttostr(qTekLst.recordcount) + ')';
  wwDBGrid1.SetFocus;

end;

procedure Ttekdurum.CheckBox7Click(Sender: TObject);
begin
  if CheckBox7.Checked then
  begin
    arzturu.Enabled := true;
    arzturu.SetFocus;
  end
  else
  begin
    arzturu.value := '';
    arzturu.Enabled := False;
  end;

end;

procedure Ttekdurum.CheckBox8Click(Sender: TObject);
begin
  if CheckBox8.Checked then
  begin
    yapisl.Enabled := true;
    yapisl.SetFocus;
  end
  else
  begin
    yapisl.value := '';
    yapisl.Enabled := False;
  end;

end;

procedure Ttekdurum.CheckBox10Click(Sender: TObject);
begin
  if CheckBox10.Checked then
  begin
    filtre1k.Enabled := true;
    filtre1k.SetFocus;
  end
  else
  begin
    filtre1k.value := '';
    filtre1k.Enabled := False;
  end;

end;

procedure Ttekdurum.CheckBox1Click(Sender: TObject);
begin
  if CheckBox1.Checked then
  begin
    filtre2k.Enabled := true;
    filtre2k.SetFocus;
  end
  else
  begin
    filtre2k.value := '';
    filtre2k.Enabled := False;
  end;

end;

procedure Ttekdurum.CheckBox13Click(Sender: TObject);
begin
  if CheckBox13.Checked then
  begin
    filtre4k.Enabled := true;
    filtre4k.SetFocus;
  end
  else
  begin
    filtre4k.value := '';
    filtre4k.Enabled := False;
  end;

end;

procedure Ttekdurum.CheckBox11Click(Sender: TObject);
begin
  if CheckBox11.Checked then
  begin
    filtre3k.Enabled := true;
    filtre3k.SetFocus;
  end
  else
  begin
    filtre3k.value := '';
    filtre3k.Enabled := False;
  end;

end;

procedure Ttekdurum.FormShow(Sender: TObject);
begin
  tekniktipi.close;
  tekniktipi.open;

  arizaturu.close;
  arizaturu.open;

  yapilanislem.close;
  yapilanislem.open;

  grup.close;
  grup.open;

  model.close;
  model.open;

  marka.close;
  marka.open;

  agrup.close;
  agrup.open;

end;

procedure Ttekdurum.reportPreviewFormCreate(Sender: TObject);
begin
  Report.PreviewForm.WindowState := wsMaximized;
  //  TppViewer(Report.PreviewForm.Viewer).ZoomSetting := zs100Percent;

end;

procedure Ttekdurum.wwDBGrid1CalcTitleImage(Sender: TObject; Field: TField;
  var TitleImageAttributes: TwwTitleImageAttributes);
begin
  if field.FieldName = sira then
  begin
    if bk = ' ASC' then
      TitleImageAttributes.imageIndex := 0
    else
      TitleImageAttributes.imageIndex := 1;
  end
  else
    TitleImageAttributes.imageIndex := -1;

end;

procedure Ttekdurum.wwDBGrid1TitleButtonClick(Sender: TObject;
  AFieldName: string);
begin
  if AFieldName = sira then
  begin
    if bk = ' ASC' then
      bk := ' DESC'
    else
      bk := ' ASC';
  end
  else
    bk := ' ASC';
  sira := AFieldName;
  qTekLst.Sort := sira + bk;
  wwDBGrid1.RedrawGrid;

end;

procedure Ttekdurum.filtre1kChange(Sender: TObject);
begin
  filtre2k.Text := '';
  filtre3k.Text := '';
  filtre4k.Text := '';

end;

procedure Ttekdurum.filtre2kChange(Sender: TObject);
begin
  filtre3k.Text := '';
  filtre4k.Text := '';

end;

procedure Ttekdurum.filtre3kChange(Sender: TObject);
begin
  filtre4k.Text := '';

end;

procedure Ttekdurum.filtre2kEnter(Sender: TObject);
begin
  model.Close;
  model.SQL.Text := 'select distinct modeladi from stmodel where 1=1 ';
  if filtre1k.LookupValue <> '' then
    model.SQL.Add(' and (select isim from stgrup where stgrup.grkod=stmodel.grubno)=' + #39 + filtre1k.LookupValue + #39);
  model.SQL.add(' order by modeladi');
  model.Open;

end;

procedure Ttekdurum.filtre3kEnter(Sender: TObject);
begin
  marka.Close;
  marka.SQL.Text := 'select distinct markadi from stmarkag where 1=1 ';
  if filtre2k.LookupValue <> '' then
    marka.SQL.Add(' and (select modeladi from stmodel where stmodel.modelid=stmarkag.modelno)=' + #39 + filtre2k.LookupValue + #39);
  if filtre1k.LookupValue <> '' then
    marka.SQL.Add(' and (select isim from stgrup where stgrup.grkod=(select grubno from stmodel where stmodel.modelid=stmarkag.modelno))=' + #39 + filtre1k.LookupValue + #39);
  marka.SQL.add(' order by markadi');
  marka.Open;

end;

procedure Ttekdurum.filtre4kEnter(Sender: TObject);
begin
  agrup.Close;
  agrup.SQL.Text := 'select distinct isim from stagrup where 1=1 ';
  if filtre3k.LookupValue <> '' then
    agrup.SQL.Add(' and (select markadi from stmarkag where stmarkag.markaid=stagrup.markano)=' + #39 + filtre3k.LookupValue + #39);
  if filtre2k.LookupValue <> '' then
    agrup.SQL.Add(' and (select modeladi from stmodel where stmodel.modelid=(select modelno from stmarkag where stmarkag.markaid=stagrup.markano))=' + #39 + filtre2k.LookupValue + #39);
  if filtre1k.LookupValue <> '' then
    agrup.SQL.Add(' and (select isim from stgrup where stgrup.grkod=(select grubno from stmodel where stmodel.modelid=(select modelno from stmarkag where stmarkag.markaid=stagrup.markano)))=' + #39 + filtre1k.LookupValue + #39);
  agrup.SQL.add(' order by isim');
  agrup.Open;

end;

end.

